Create PROC [dbo].[STILoadInvoiceForSendToTaxSystem]
    @Startdate VARCHAR(10),
    @EndDate VARCHAR(10),
    @StartTime VARCHAR(10),
    @EndTime VARCHAR(10),
    @TypeFactor TINYINT,
    @ComputerName NVARCHAR(MAX),
    @InvoiceNumber VARCHAR(10)
AS
BEGIN
    IF @InvoiceNumber = 0
        SET @InvoiceNumber = NULL;


    IF EXISTS
    (
        SELECT *
        FROM [tempdb].dbo.sysobjects
        WHERE name = '##CacheFactor'
              AND xtype = 'U'
    )
    BEGIN
        DELETE FROM [tempdb].dbo.##CacheFactor
        WHERE ComputerName = @ComputerName;
    END;
    ELSE
    BEGIN
        CREATE TABLE ##CacheFactor
        (
            Id BIGINT,
            InvoiceType TINYINT,
            InvoiceNumber VARCHAR(10) COLLATE Arabic_CI_AS,
            SenderName NVARCHAR(35) COLLATE Arabic_CI_AS,
            RecipientName NVARCHAR(35) COLLATE Arabic_CI_AS,
            TotalPrice MONEY,
            FactorDate VARCHAR(10) COLLATE Arabic_CI_AS,
            TimeFactor VARCHAR(5) COLLATE Arabic_CI_AS,
            IsSend BIT
                DEFAULT (1),
            DeductionFactor MONEY,
            IncrementFactor MONEY,
            ReceiptAndPaymentPrice MONEY,
            liquidationFactor MONEY,
            ComputerName NVARCHAR(MAX) COLLATE Arabic_CI_AS
        );
    END;


    DECLARE @THeadh TABLE
    (
        id_havaleh BIGINT,
        Kind_flag CHAR(1),
        InvoiceNumber VARCHAR(8),
        Trans_Name NVARCHAR(35),
        Recive_Name NVARCHAR(35),
        TotalPrice MONEY,
        FactorDate VARCHAR(10),
        TimeFactor VARCHAR(5)
		
    );

    INSERT INTO @THeadh
    SELECT headH.Id_Havaleh,
           Kind_Flag,
           CASE
               WHEN (Kind_Flag IN ( 5, 7, 9 )) THEN
                   Nu_Forosh
               WHEN Kind_Flag IN ( 0, 6, 8 ) THEN
                   Nu_Bargasht_Forosh
           END AS InvoiceNumber,
           Trans_Name,
           Recive_Name,
           CASE
               WHEN Kind_Flag IN ( 8 ) THEN
                   Tot_Price_KHarid
               WHEN Kind_Flag IN ( 0, 5, 6, 7, 9 ) THEN
                   Tot_Price_forosh
           END AS TotalPrice,
           dbo.MiladiToShamsi(CONVERT(VARCHAR(10), Tarikh_faktor, 111)) AS FactorDate,
           CONVERT(VARCHAR(5), Tarikh, 108) TimeFactor
 

    FROM headH WITH (NOLOCK)
    WHERE Kind_Flag = @TypeFactor
          AND CONVERT(VARCHAR(10), Tarikh_faktor, 111)
          BETWEEN (@Startdate) AND (@EndDate)
          AND CONVERT(VARCHAR(10), Tarikh, 108)
          BETWEEN @StartTime AND @EndTime
          AND (NOT EXISTS
    (
        SELECT *
        FROM dbo.STITransaction WITH (NOLOCK)
        WHERE InvoiceId = dbo.headH.Id_Havaleh
              AND Type = CASE
                             WHEN @TypeFactor IN ( 7, 5, 9 ) THEN
                                 5
                             WHEN @TypeFactor IN ( 8, 6, 0 ) THEN
                                 6
                         END
    )
              );

    --///////////////////////////////////////////////////////////////////////////////

    DECLARE @TOver_Under TABLE
    (
        id_havaleh BIGINT,
        IncrementFactor MONEY,
		DeductionFactor MONEY
    );
    INSERT INTO @TOver_Under
    SELECT Over_Under_Factor.Id_Havaleh,
           SUM(   CASE
                      WHEN Type = 1 THEN
                          ROUND(Price, 0)
                      ELSE
                          0
                  END
              ) IncrementFactor,
           SUM(   CASE
                      WHEN Type = 0 THEN
                          ROUND(Price, 0)
                      ELSE
                          0
                  END
              ) DeductionFactor
    FROM Over_Under_Factor WITH (NOLOCK)
        INNER JOIN
        (
            SELECT headH.Id_Havaleh
            FROM headH WITH (NOLOCK)
            WHERE Kind_Flag = @TypeFactor
                  AND CONVERT(VARCHAR(10), Tarikh_faktor, 111)
                  BETWEEN (@Startdate) AND (@EndDate)
                  AND CONVERT(VARCHAR(10), Tarikh, 108)
                  BETWEEN @StartTime AND @EndTime
        ) AS TbHeadh
            ON TbHeadh.Id_Havaleh = Over_Under_Factor.Id_Havaleh
    WHERE NOT EXISTS
    (
        SELECT *
        FROM dbo.STITransaction WITH (NOLOCK)
        WHERE InvoiceId = dbo.Over_Under_Factor.Id_Havaleh
              AND Type = CASE
                             WHEN @TypeFactor IN ( 7, 5, 9 ) THEN
                                 5
                             WHEN @TypeFactor IN ( 8, 6, 0 ) THEN
                                 6
                         END
    )
    GROUP BY dbo.Over_Under_Factor.Id_Havaleh;
    --///////////////////////////////////////////////////////////////////////////////

    DECLARE @ReceiptAndPayment TABLE
    (
        Id_havaleh BIGINT,
        ReceiptAndPaymentPrice MONEY
    );

    INSERT INTO @ReceiptAndPayment
    SELECT HeadC.Id_Havaleh,
           SUM(Ch_Price) ReceiptAndPaymentPrice
    FROM Cheqe WITH (NOLOCK)
        INNER JOIN HeadC WITH (NOLOCK)
            ON HeadC.Id_Cheqe = Cheqe.Id_Cheqe
        INNER JOIN
        (
            SELECT headH.Id_Havaleh
            FROM headH WITH (NOLOCK)
            WHERE Kind_Flag = @TypeFactor
                  AND CONVERT(VARCHAR(10), Tarikh_faktor, 111)
                  BETWEEN (@Startdate) AND (@EndDate)
                  AND CONVERT(VARCHAR(10), Tarikh, 108)
                  BETWEEN @StartTime AND @EndTime
        ) AS TbHeadh2
            ON TbHeadh2.Id_Havaleh = HeadC.Id_Havaleh
    WHERE NOT EXISTS
    (
        SELECT *
        FROM dbo.STITransaction WITH (NOLOCK)
        WHERE InvoiceId = dbo.HeadC.Id_Havaleh
              AND Type = CASE
                             WHEN @TypeFactor IN ( 7, 5, 9 ) THEN
                                 5
                             WHEN @TypeFactor IN ( 8, 6, 0 ) THEN
                                 6
                         END
    )
    GROUP BY HeadC.Id_Havaleh;

    --///////////////////////////////////////////////////////////////////////////////
    INSERT INTO ##CacheFactor
    (
        Id,
        InvoiceType,
        InvoiceNumber,
        SenderName,
        RecipientName,
        TotalPrice,
        FactorDate,
        TimeFactor,
        IsSend,
        DeductionFactor,
        IncrementFactor,
        ReceiptAndPaymentPrice,
        liquidationFactor,
        ComputerName
    )
    SELECT THeadh.id_havaleh,
           THeadh.Kind_flag,
           THeadh.InvoiceNumber,
           THeadh.Trans_Name,
           THeadh.Recive_Name,
           THeadh.TotalPrice,
           THeadh.FactorDate,
           THeadh.TimeFactor,
           0,
           TOver_Under.DeductionFactor,
           TOver_Under.IncrementFactor,
           ISNULL(ReceiptAndPaymentPrice,0) ,
           (THeadh.TotalPrice + ISNULL(IncrementFactor - DeductionFactor, 0)) - ISNULL(ReceiptAndPaymentPrice, 0) liquidationFactor,
           @ComputerName AS ComputerName
    FROM
    (SELECT * FROM @THeadh WHERE InvoiceNumber = ISNULL(@InvoiceNumber, InvoiceNumber)   ) AS THeadh
        LEFT OUTER JOIN
        (SELECT * FROM @TOver_Under) AS TOver_Under
            ON TOver_Under.id_havaleh = THeadh.id_havaleh
        LEFT OUTER JOIN
        (SELECT * FROM @ReceiptAndPayment) AS ReceiptAndPayment
            ON ReceiptAndPayment.Id_havaleh = THeadh.id_havaleh;

END;



